-----20100608 OA系统需要使用视图进行界面枚举显示
CREATE VIEW V_STRU_ORGAN AS SELECT
 O.ORGAN_ID,
 O.ORGAN_CODE,
 O.ORGAN_NAME,
 O.SHORT_NAME,
 O.ORGAN_TYPE,
 S.STRU_ID,
 S.STRU_TYPE,
 S.PARENT_ID
 FROM PUB_STRU S,PUB_ORGAN O WHERE  S.ORGAN_ID=O.ORGAN_ID ;


--20111122 工作流流程统计分析需要查询员工及其对应上级组织机构的组织编号和组织名称
CREATE VIEW V_EMPLOYEE_SUPERIOR AS
SELECT F.ORGAN_ID,F.ORGAN_NAME,F.UPPER_ORGAN_ID,E.ORGAN_NAME AS UPPER_ORGAN_NAME,E.ORGAN_TYPE AS UPPER_ORGAN_TYPE FROM PUB_ORGAN E JOIN
(
  SELECT D.ORGAN_ID,D.ORGAN_NAME,C.UPPER_ORGAN_ID  FROM PUB_ORGAN D JOIN
  (  SELECT A.ORGAN_ID,B.ORGAN_ID AS UPPER_ORGAN_ID
     FROM PUB_STRU A,PUB_STRU B
	 WHERE INSTR(A.STRU_PATH,B.STRU_PATH)>0 AND A.IN_USE='1' AND A.STRU_TYPE='00'
  ) C ON D.ORGAN_ID=C.ORGAN_ID
  WHERE SUBSTR(D.ORGAN_TYPE,1,1)='8'
) F ON E.ORGAN_ID=F.UPPER_ORGAN_ID;

--创建行政组织类型视图

--CREATE VIEW V_ADMIN_ORGAN_TYPE AS
--SELECT ORGAN_TYPE,ORGAN_NAME FROM PUB_ORGAN_TYPE WHERE ORGAN_TYPE IN('1','2','8');

--创建业务组织类型视图
--CREATE VIEW V_BIZ_ORGAN_TYPE AS
--SELECT ORGAN_TYPE AS BIZ_ORGAN_TYPE,TYPE_NAME AS BIZ_TYPE_NAME,PARENT_TYPE FROM PUB_ORGAN_TYPE WHERE ORGAN_TYPE<>PARENT_TYPE AND IN_USE='1' start with organ_type in ('1','2','6') connect by NOCYCLE prior organ_type=parent_type;
--创建岗位类型视图
--CREATE VIEW V_POST_TYPE AS
--SELECT ORGAN_TYPE AS POST_TYPE,TYPE_NAME AS POST_TYPE_NAME,PARENT_TYPE FROM PUB_ORGAN_TYPE WHERE ORGAN_TYPE<>PARENT_TYPE AND IN_USE='1' start with organ_type ='6'  connect by NOCYCLE prior organ_type=parent_type;

-- 查询数据权限
CREATE OR REPLACE VIEW V_USER_DATA_PERMIT AS
-- 查询范围节点下的单位、部门和员工
SELECT UD2.USER_ID,S2.ORGAN_ID
FROM PUB_STRU S2, PUB_USER_DATA UD2, PUB_STRU S3
WHERE EXISTS (SELECT 1
  -- 范围节点
		FROM (SELECT S1.STRU_PATH
				FROM PUB_USER_DATA UD, PUB_STRU S1
			   WHERE UD.CODE = S1.ORGAN_ID
				 AND UD.STRU_TYPE = S1.STRU_TYPE
				 AND UD.IS_SCOPE = '1') T_SCOPE
	   WHERE S2.STRU_PATH LIKE T_SCOPE.STRU_PATH || '%')
 AND S2.ORGAN_ID IS NOT NULL
 AND UD2.CODE = S3.ORGAN_ID AND UD2.STRU_TYPE = S3.STRU_TYPE
 AND S2.STRU_PATH LIKE S3.STRU_PATH || '%'
UNION
-- 范围节点下的岗位
SELECT UD2.USER_ID, S2.POSITION_CODE
 FROM PUB_STRU S2, PUB_USER_DATA UD2, PUB_STRU S3
WHERE EXISTS (SELECT 1
	   -- 范围节点
		 FROM (SELECT S1.STRU_PATH
				 FROM PUB_USER_DATA UD, PUB_STRU S1
				WHERE UD.CODE = S1.ORGAN_ID
				  AND UD.STRU_TYPE = S1.STRU_TYPE
				  AND UD.IS_SCOPE = '1') T_SCOPE
		WHERE S2.STRU_PATH LIKE T_SCOPE.STRU_PATH || '%')
  AND S2.ORGAN_ID IS NOT NULL
  AND S2.POSITION_CODE IS NOT NULL
  AND UD2.CODE = S3.ORGAN_ID
  AND UD2.STRU_TYPE = S3.STRU_TYPE
  AND S2.STRU_PATH LIKE S3.STRU_PATH || '%'
UNION
-- 实例节点
SELECT UD.USER_ID, UD.CODE ORGAN_ID
FROM PUB_USER_DATA UD
WHERE UD.IS_SCOPE = '0'
-- 用户的岗位
UNION
SELECT UE.USER_ID, P.POSITION_CODE ORGAN_ID
  FROM PUB_POSITION P, PUB_STRU S, PUB_STRU US, PUB_USER_EMPLOYEE UE
 WHERE P.POSITION_CODE = S.POSITION_CODE
   AND US.STRU_ID = UE.EMPLOYEE_ID
   AND S.ORGAN_ID = US.ORGAN_ID
-- 用户岗位的所辖岗位
UNION
SELECT UE.USER_ID, P.POSITION_CODE  ORGAN_ID
  FROM PUB_POSITION      P,
	   PUB_POSITION      P2,
	   PUB_STRU          S,
	   PUB_STRU          S2,
	   PUB_USER_EMPLOYEE UE
 WHERE P.P_POSITION_CODE = P2.POSITION_CODE
   AND P2.POSITION_CODE = S.POSITION_CODE
   AND S.ORGAN_ID = S2.ORGAN_ID
   AND S.STRU_TYPE != '00'
   AND S2.STRU_TYPE = '00'
   AND S2.STRU_ID = UE.EMPLOYEE_ID
   AND EXISTS (SELECT 1
		  FROM PUB_POSITION PP
		 WHERE P.P_POSITION_CODE = PP.POSITION_CODE);
   
-- 查询功能权限
CREATE OR REPLACE VIEW V_USER_FUNCTION_PERMIT AS
  SELECT DISTINCT UR.TARGET USER_ID, O.FUNCTION_CODE
    FROM PUB_USER_ROLE UR, PUB_ROLE_OPERATION RO, PUB_OPERATIONS O
   WHERE RO.OPERATION_CODE = O.OPERATION_CODE
     AND UR.ROLE_ID = RO.ROLE_ID;